package com.dao;

import com.entity.ExamrecordEntity;
import com.baomidou.mybatisplus.mapper.BaseMapper;
import java.util.List;
import java.util.Map;
import com.baomidou.mybatisplus.mapper.Wrapper;
import com.baomidou.mybatisplus.plugins.pagination.Pagination;

import org.apache.ibatis.annotations.Param;
import com.entity.vo.ExamrecordVO;
import com.entity.view.ExamrecordView;
import org.apache.ibatis.annotations.Select;


/**
 * 考试记录表
 * 
 * @author 
 * @email 
 * @date 2022-03-18 10:34:29
 */
public interface ExamrecordDao extends BaseMapper<ExamrecordEntity> {
	
	List<ExamrecordVO> selectListVO(@Param("ew") Wrapper<ExamrecordEntity> wrapper);
	
	ExamrecordVO selectVO(@Param("ew") Wrapper<ExamrecordEntity> wrapper);
	
	List<ExamrecordView> selectListView(@Param("ew") Wrapper<ExamrecordEntity> wrapper);

	List<ExamrecordView> selectListView(Pagination page,@Param("ew") Wrapper<ExamrecordEntity> wrapper);
	
	ExamrecordView selectView(@Param("ew") Wrapper<ExamrecordEntity> wrapper);
	
	List<ExamrecordView> selectGroupBy(Pagination page,@Param("ew") Wrapper<ExamrecordEntity> wrapper);

	/**
	 * 获取成绩分布(分4个等级)
	 */
	@Select("SELECT tmp.level, COUNT(*) as count FROM (" +
			"  SELECT userid, " +
			"  CASE" +
			"    WHEN SUM(myscore) >= 90 THEN '优秀'" +
			"    WHEN SUM(myscore) >= 80 AND SUM(myscore) < 90 THEN '良好'" +
			"    WHEN SUM(myscore) >= 60 AND SUM(myscore) < 80 THEN '及格'" +
			"    ELSE '不及格'" +
			"  END as level " +
			"  FROM examrecord " +
			"  GROUP BY userid" +
			") tmp " +
			"GROUP BY tmp.level")
	List<Map<String, Object>> getScoreDistribution();

	/**
	 * 获取科目平均分(最近4个科目)
	 */
	@Select("SELECT " +
			"s.name, " +
			"ROUND(SUM(e.myscore), 1) as avg_score " +
			"FROM examrecord e " +
			"JOIN student s ON e.userid = s.id " +
			"GROUP BY e.userid, s.name " +
			"ORDER BY avg_score DESC " +
			"LIMIT 4")
	List<Map<String, Object>> selectStudentAvgScores();
}
